-- Create table scripts for CRICKET schema in ClubDB


------------------------------------------------------------------------------------
-- SCHEMA: cricket
------------------------------------------------------------------------------------
USE [ClubDB]
GO
CREATE SCHEMA [cricket] AUTHORIZATION [owner.clubdb]

------------------------------------------------------------------------------------
-- TABLE:	 cricket.Seasons
------------------------------------------------------------------------------------
USE [ClubDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [cricket].[Seasons](
	[SeasonId] [int] IDENTITY(1,1) NOT NULL,
	[SeasonName] [varchar](100) NULL,
	[SeasonStart] [datetime] NULL,
	[SeasonEnd] [datetime] NULL,
 CONSTRAINT [PK_Seasons_1] PRIMARY KEY CLUSTERED 
(
	[SeasonId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Represents an instance of a cricket season' , @level0type=N'SCHEMA',@level0name=N'cricket', @level1type=N'TABLE',@level1name=N'Seasons'

------------------------------------------------------------------------------------
-- TABLE:	cricket.Players
------------------------------------------------------------------------------------
USE [ClubDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [cricket].[Players](
	[PlayerId] [int] IDENTITY(1,1) NOT NULL,
	[PlayerName] [varchar](255) NULL,
	[MemberKey] [uniqueidentifier] NULL,
 CONSTRAINT [PK_Players_1] PRIMARY KEY CLUSTERED 
(
	[PlayerId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Each player has a record' , @level0type=N'SCHEMA',@level0name=N'cricket', @level1type=N'TABLE',@level1name=N'Players'


------------------------------------------------------------------------------------
-- TABLE:	cricket.Teams
-- REQ.		cricket.Players
------------------------------------------------------------------------------------
USE [ClubDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [cricket].[Teams](
	[TeamId] [int] IDENTITY(1,1) NOT NULL,
	[TeamName] [varchar](255) NOT NULL,
	[CaptainId] [int] NULL,
	[ViceCaptainId] [int] NULL,
 CONSTRAINT [PK_Teams_1] PRIMARY KEY CLUSTERED 
(
	[TeamId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Cricket Teams at the club, like 1st, 2nd under 15''s etc.' , @level0type=N'SCHEMA',@level0name=N'cricket', @level1type=N'TABLE',@level1name=N'Teams'
GO
ALTER TABLE [cricket].[Teams]  WITH CHECK ADD  CONSTRAINT [FK_Teams_Players] FOREIGN KEY([CaptainId])
REFERENCES [cricket].[Players] ([PlayerId])
GO
ALTER TABLE [cricket].[Teams] CHECK CONSTRAINT [FK_Teams_Players]
GO
ALTER TABLE [cricket].[Teams]  WITH CHECK ADD  CONSTRAINT [FK_Teams_Players1] FOREIGN KEY([ViceCaptainId])
REFERENCES [cricket].[Players] ([PlayerId])
GO
ALTER TABLE [cricket].[Teams] CHECK CONSTRAINT [FK_Teams_Players1]


------------------------------------------------------------------------------------
-- TABLE:	cricket.PlayerStats
-- REQ.		cricket.Player
------------------------------------------------------------------------------------
USE [ClubDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [cricket].[PlayerStats](
	[PlayerStatId] [int] IDENTITY(1,1) NOT NULL,
	[PlayerId] [int] NOT NULL,
	[MatchId] [int] NOT NULL,
	[BatNumber] [int] NULL,
	[RunsScored] [int] NULL,
	[BallsFaced] [int] NULL,
	[MinutesInBat] [int] NULL,
	[FoursHit] [int] NULL,
	[SixesHit] [int] NULL,
	[HowOut] [varchar](50) NULL,
	[BowlersName] [varchar](100) NULL,
	[NotOut] [bit] NOT NULL CONSTRAINT [DF_PlayerStats_NotOut]  DEFAULT ((0)),
	[DNB] [bit] NOT NULL CONSTRAINT [DF_PlayerStats_DNB]  DEFAULT ((0)),
	[BowlerNumber] [int] NULL,
	[OversBowled] [float] NULL,
	[Maidens] [int] NULL,
	[Wickets] [int] NULL,
	[Runs] [int] NULL,
	[Catches] [int] NULL,
 CONSTRAINT [PK_PlayerStats] PRIMARY KEY CLUSTERED 
(
	[PlayerStatId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Players Stats for each match' , @level0type=N'SCHEMA',@level0name=N'cricket', @level1type=N'TABLE',@level1name=N'PlayerStats'
GO
ALTER TABLE [cricket].[PlayerStats]  WITH CHECK ADD  CONSTRAINT [FK_PlayerStats_Matches] FOREIGN KEY([MatchId])
REFERENCES [cricket].[Matches] ([MatchId])
GO
ALTER TABLE [cricket].[PlayerStats] CHECK CONSTRAINT [FK_PlayerStats_Matches]
GO
ALTER TABLE [cricket].[PlayerStats]  WITH CHECK ADD  CONSTRAINT [FK_PlayerStats_Players] FOREIGN KEY([PlayerId])
REFERENCES [cricket].[Players] ([PlayerId])
GO
ALTER TABLE [cricket].[PlayerStats] CHECK CONSTRAINT [FK_PlayerStats_Players]


------------------------------------------------------------------------------------
-- TABLE:	 cricket.Matches
-- REQ.:	 cricket.Seasons, cricket.Teams
------------------------------------------------------------------------------------
USE [ClubDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [cricket].[Matches](
	[MatchId] [int] IDENTITY(1,1) NOT NULL,
	[MatchDate] [datetime] NULL,
	[SeasonId] [int] NOT NULL,
	[TeamId] [int] NOT NULL,
	[TeamOvers] [int] NULL,
	[TeamRuns] [int] NULL,
	[TeamWickets] [int] NULL,
	[OpponentTeam] [varchar](255) NULL,
	[OppOvers] [int] NULL,
	[OppRuns] [int] NULL,
	[OppWickets] [int] NULL,
	[Result] [varchar](10) NULL CONSTRAINT [DF_Matches_Result]  DEFAULT ('-'),
	[ResultComment] [varchar](100) NULL,
	[TimeFinished] [varchar](20) NULL,
	[IsHome] [bit] NULL,
	[Points] [int] NULL,
 CONSTRAINT [PK_Matches] PRIMARY KEY CLUSTERED 
(
	[MatchId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Instance of a cricket match and related data' , @level0type=N'SCHEMA',@level0name=N'cricket', @level1type=N'TABLE',@level1name=N'Matches'
GO
ALTER TABLE [cricket].[Matches]  WITH CHECK ADD  CONSTRAINT [FK_Matches_Seasons] FOREIGN KEY([SeasonId])
REFERENCES [cricket].[Seasons] ([SeasonId])
GO
ALTER TABLE [cricket].[Matches] CHECK CONSTRAINT [FK_Matches_Seasons]
GO
ALTER TABLE [cricket].[Matches]  WITH CHECK ADD  CONSTRAINT [FK_Matches_Teams] FOREIGN KEY([TeamId])
REFERENCES [cricket].[Teams] ([TeamId])
GO
ALTER TABLE [cricket].[Matches] CHECK CONSTRAINT [FK_Matches_Teams]